from datetime import date, datetime
import xlrd
from db_sql import insert_db
from utils import exr, oil, pc, ab, pc_abs, pom, cop, alu0, alu1, ste


def run():
    xlrd.Book.encoding = "gbk"  # 设置GBK编码
    excelPath = "files\\rawdatas.xlsx"
    data = xlrd.open_workbook(excelPath)
    db_dicts = {}
    lists = [exr, oil, pc, ab, pc_abs, pom, cop, alu0, alu1, ste]
    for num in lists:
        table = data.sheet_by_index(lists.index(num))
        nrows = table.nrows
        for i in range(1, nrows):
            try:
                issudate = table.cell(i, 0).value
                data_value = xlrd.xldate_as_tuple(issudate, data.datemode)
                tmp = date(*data_value[:3]).strftime('%Y/%m/%d')
                db_dicts["price_date"] = tmp
                row_lists = table.row_values(i)
                db_dicts["avg_price"] = row_lists[1]
                db_dicts["lower_price"] = row_lists[2]
                db_dicts["higher_price"] = row_lists[3]
                # db_dicts["avg_price"] = str((float(row_lists[1]) + float(row_lists[2])) / 2)
                db_dicts["unit"] = row_lists[4]
                db_dicts["raw_name"] = row_lists[5]
                db_dicts["source"] = row_lists[6]
                db_dicts["change_amount"] = row_lists[7]
                db_dicts["change_range"] = row_lists[8]
                db_dicts["id"] = tmp.replace("/", "") + num
                db_dicts["raw_type"] = num
                db_dicts["create_time"] = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
                insert_db(db_dicts)
            except:
                pass


if __name__ == '__main__':
    run()